IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.CreateDefaultConstraint'))
BEGIN
	DROP PROCEDURE MD3.CreateDefaultConstraint
END
GO


CREATE Procedure MD3.CreateDefaultConstraint 

( 
     @SchemaName		        SYSNAME
    ,@TableName	    	        SYSNAME
    ,@ColumnName		        SYSNAME
    ,@ConstraintName         	SYSNAME
    -- Note: Character string constants must have the single quotes TRIPLED UP. However,
    -- use single quotes for function calls and for columns that are integers. Examples:
    --  @DefaultExpression = '''N/A''' -- String Data
    --  @DefaultExpression = '''1/1/2000''' -- Hardcoded Date
    --  @DefaultExpression = '1' -- Integer
    --  @DefaultExpression = 'GETUTCDATE()' -- Function Call
    ,@Expression         VARCHAR(MAX)
	,@DropConstraint			BIT = 0
)
AS    

BEGIN

    SET NOCOUNT ON  

    DECLARE @Msg                 VARCHAR(MAX) , @DropSQL VARCHAR(MAX), @CreateSQL VARCHAR(MAX)
           ,@SQL                 VARCHAR(MAX)  
           ,@FormattedColumnData VARCHAR(MAX)  
           ,@ColumnDataType      SYSNAME
    DECLARE @NewExpression VARCHAR(MAX)
    DECLARE @CurrentConstraintName VARCHAR(255)
    DECLARE @CurrentConstraintExpression VARCHAR(MAX)
    DECLARE @CurrentColumnName VARCHAR(255)

	--prep the DROP and ADD commands
	SELECT @DropSQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT ' + quotename(@ConstraintName) + ';';
	SELECT @ColumnDataType = MD3.f_GetColumnDataType(@SchemaName, @TableName, @ColumnName)
	IF (@ColumnDataType = 'BINARY' OR @ColumnDataType = 'VARBINARY')
    BEGIN
        SET @CreateSQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' ADD CONSTRAINT ' + quotename(@ConstraintName) + ' DEFAULT CAST(' + @Expression + ' AS BINARY) FOR ' + @ColumnName;
    END
    ELSE
    BEGIN
        SET @CreateSQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' ADD CONSTRAINT ' + quotename(@ConstraintName) + ' DEFAULT ' + @Expression + ' FOR ' + @ColumnName;
    END;

	--if we were requested to DROP the constraint, do it.
	IF @DropConstraint = 1
	BEGIN
		IF EXISTS (
			SELECT 1
			FROM MD3.f_GetConstraints (@SchemaName,@TableName)
			WHERE ConstraintName = @ConstraintName
		)
		BEGIN
			EXEC MD3.ExecuteSQL @DropSQL;
		END;

		RETURN 0;
	END;

	 --Does the constraint exist by definition and not name?
	 --If so, rename it. 
	 --Constraint names must be unique for all tables in a given schema. 
	SELECT @CurrentConstraintName = ConstraintName
	FROM MD3.f_GetConstraints (@SchemaName,@TableName)
	WHERE ConstraintExpression = @Expression
	AND ConstraintType = 'DEFAULT'
	AND ColumnName = @ColumnName
	AND ConstraintName <> @ConstraintName

	IF @CurrentConstraintName IS NOT NULL
	BEGIN
		EXEC MD3.InsertLogMessage
			@Message = 'The constraint definition already exists, but is a different name.  Renaming...',
			@Severity = 'I';
		SELECT @SQL = 'sp_rename ''' + @SchemaName + '.' + @CurrentConstraintName + ''', ''' + @ConstraintName + ''''
		EXEC MD3.ExecuteSQL @SQL;
		RETURN;
	END;

	--Does the constraint exist by both definition and name?  
	--If so, RETURN, nothing to do.  
	IF EXISTS (
		SELECT 1
		FROM MD3.f_GetConstraints (@SchemaName,@TableName)
		WHERE ConstraintExpression = @Expression
		AND ConstraintType = 'DEFAULT'
		AND ColumnName = @ColumnName
		AND ConstraintName = @ConstraintName
	)
	BEGIN
		EXEC MD3.InsertLogMessage
			@Message = 'Constraint already exists, no work to do.',
			@Severity = 'I';
		RETURN;
	END;


	--Does this constraint exist by name but definition *might* be different?
	--we have to do various things in this case to prep it for re-creation.  
    SELECT @CurrentConstraintExpression = ConstraintExpression
          ,@CurrentColumnName = ColumnName
    FROM MD3.f_GetConstraints (@SchemaName,@TableName)
    WHERE ConstraintName = @ConstraintName
	AND ConstraintType = 'DEFAULT';

    IF (@CurrentConstraintExpression IS NOT NULL)
    BEGIN

        -- See if it is defined on the right column, if not, remove it.  
        IF (@CurrentColumnName <> @ColumnName)
        BEGIN
			EXEC MD3.InsertLogMessage
				@Message = 'The constraint name already exists, but is on a different column.  Removing...',
				@Severity = 'I';
			EXEC MD3.ExecuteSQL @DropSQL;
        END;

        -- SQL Server optimizes and re-formats the input expression before storing it in the system 
        -- catalog. This includes converting "IN" clauses to "OR" statements and adding parenthesis 
        -- around data values. Etc, etc. 
		
		IF @CurrentConstraintExpression <> @Expression
		BEGIN
			EXEC MD3.InsertLogMessage
				@Message = 'The constraint name exists, but with a different definition. Removing the old definition.',
				@Severity = 'I';
			EXEC MD3.ExecuteSQL @DropSQL;
		END;
	END;

	--at this point we can create the constraint as requested.  
	EXEC MD3.InsertLogMessage
		@Message = 'Creating the constraint.',
		@Severity = 'I';
	EXEC MD3.ExecuteSQL @CreateSQL;

	--now that we've created a constraint let's get the reformatted expression from SQL Server.  
	SELECT @NewExpression = ConstraintExpression
	FROM MD3.f_GetConstraints (@SchemaName,@TableName)
	WHERE ConstraintName = @ConstraintName
   
	--if the new expression is not what was passed, then let's throw a warning for the developer to 
	--fix @Expression so that we are not constantly dropping/adding the same constraint definition
	IF @NewExpression <> @Expression 
	BEGIN
		SELECT @Msg = 'Created constraint: ' + @ConstraintName + ' but the requested @Expression: ' + @Expression + ' does not match'
		SELECT @Msg += ' the actual Expression: ' + @NewExpression + '. You should fix Expression to match the actual:  ' + @NewExpression
		EXEC MD3.InsertLogMessage
			@Message = @Msg,
			@Severity = 'W';
	END;
END;
GO
